package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pojo.Company;
import pojo.TimeNode;

public class DBOperation {
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	private Connection con = null;

	public boolean addCompany(Company company) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "insert into qiyexinxi (id,qiyemingcheng,jianshedanwei,zhandi,zhuceziben,zongtouzi,nianchanzhi,lirun,shuishou,yonggong,"
				+ "baoxiangmufuzeren,qiyelianxiren,lianxirendianhua,beizhu,jianjie,zhucewenjian,zhuceshijian,tudiyushenwenjian,"
				+ "tudiyushenshijian,beianwenjian,beianshijian,huanpingwenjian,huanpingshijian,"
				+ "tudichuranghetongwenjian,tudichuranghetongshijian,jiansheyongdixukewenjian,"
				+ "jiansheyongdixukeshijian,jianzhugongchengwenjian,jianzhugongchengshijian,shigongxukewenjian,"
				+ "shigongxukeshijian,wanchengtouzi,kaigongmianji,dangqianjinzhan)"
				+ "values (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, company.getQiyemingcheng());
			pst.setString(2, company.getJianshedanwei());
			pst.setString(3, company.getZhandi());
			pst.setString(4, company.getZhuceziben());
			pst.setString(5, company.getZongtouzi());
			pst.setString(6, company.getNianchanzhi());
			pst.setString(7, company.getLirun());
			pst.setString(8, company.getShuishou());
			pst.setString(9, company.getYonggong());
			pst.setString(10, company.getBaoxiangmufuzeren());
			pst.setString(11, company.getQiyelianxiren());
			pst.setString(12, company.getLianxirendianhua());
			pst.setString(13, company.getBeizhu());
			pst.setString(14, company.getJianjie());
			pst.setString(15, company.getZhucewenjian());
			pst.setString(16, company.getZhuceshijian());
			pst.setString(17, company.getTudiyushenwenjian());
			pst.setString(18, company.getTudiyushenshijian());
			pst.setString(19, company.getBeianwenjian());
			pst.setString(20, company.getBeianshijian());
			pst.setString(21, company.getHuanpingwenjian());
			pst.setString(22, company.getHuanpingshijian());
			pst.setString(23, company.getTudichuranghetongwenjian());
			pst.setString(24, company.getTudichuranghetongshijian());
			pst.setString(25, company.getJiansheyongdixukewenjian());
			pst.setString(26, company.getJiansheyongdixukeshijian());
			pst.setString(27, company.getJianzhugongchengwenjian());
			pst.setString(28, company.getJianzhugongchengshijian());
			pst.setString(29, company.getShigongxukewenjian());
			pst.setString(30, company.getShigongxukeshijian());
			pst.setString(31, company.getWanchengtouzi());
			pst.setString(32, company.getKaigongmianji());
			pst.setString(33, company.getDangqianjinzhan());
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}		
		if (!ret)
			return ret;
		
		for (String str: company.getXiangmushuxing()) {
			if (!addQiyexinxi_Xiangmushuxing(company.getQiyemingcheng(), str))
				return false;
		}
		for (String str: company.getQiyefenlei()) {
			if (!addQiyexinxi_Qiyefenlei(company.getQiyemingcheng(), str))
				return false;
		}
		return ret;
	}

	public boolean addTimeNode(String name, TimeNode timeNode) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "insert into shijianjiedian (id,qiyexinxi_id,year,month,description)"
				+ "values (null,(select id from qiyexinxi where qiyemingcheng=?),?,?,?)";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			pst.setInt(2, timeNode.getYear());
			pst.setInt(3, timeNode.getMonth());
			pst.setString(4, timeNode.getDescription());
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;
	}
	
	public boolean addQiyexinxi_Xiangmushuxing(String qiyemingcheng, String xiangmushuxing) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "insert into qiyexinxi_xiangmushuxing (id,qiyexinxi_id,xiangmushuxing_id) "
				+ "values (null,(select id from qiyexinxi where qiyemingcheng=?),"
				+ "(select id from xiangmushuxing where name=?))";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, qiyemingcheng);
			pst.setString(2, xiangmushuxing);
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;
	}
	
	public boolean addQiyexinxi_Qiyefenlei(String qiyemingcheng, String qiyefenlei) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "insert into qiyexinxi_qiyefenlei (id,qiyexinxi_id,qiyefenlei_id) "
				+ "values (null,(select id from qiyexinxi where qiyemingcheng=?),"
				+ "(select id from qiyefenlei where name=?))";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, qiyemingcheng);
			pst.setString(2, qiyefenlei);
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;
	}

	public boolean updateCompany(Company company) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "update qiyexinxi set zhandi=?,zhuceziben=?,zongtouzi=?,nianchanzhi=?,yonggong=?,"
				+ "baoxiangmufuzeren=?,qiyelianxiren=?,lianxirendianhua=?,beizhu=?,jianjie=?,zhucewenjian=?,zhuceshijian=?,tudiyushenwenjian=?,"
				+ "tudiyushenshijian=?,beianwenjian=?,beianshijian=?,huanpingwenjian=?,huanpingshijian=?,"
				+ "tudichuranghetongwenjian=?,tudichuranghetongshijian=?,jiansheyongdixukewenjian=?,"
				+ "jiansheyongdixukeshijian=?,jianzhugongchengwenjian=?,jianzhugongchengshijian=?,shigongxukewenjian=?,"
				+ "shigongxukeshijian=?,wanchengtouzi=?,kaigongmianji=?,dangqianjinzhan=?,jianshedanwei=?,lirun=?,shuishou=? "
				+ "where qiyemingcheng=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, company.getZhandi());
			pst.setString(2, company.getZhuceziben());
			pst.setString(3, company.getZongtouzi());
			pst.setString(4, company.getNianchanzhi());
			pst.setString(5, company.getYonggong());
			pst.setString(6, company.getBaoxiangmufuzeren());
			pst.setString(7, company.getQiyelianxiren());
			pst.setString(8, company.getLianxirendianhua());
			pst.setString(9, company.getBeizhu());
			pst.setString(10, company.getJianjie());
			pst.setString(11, company.getZhucewenjian());
			pst.setString(12, company.getZhuceshijian());
			pst.setString(13, company.getTudiyushenwenjian());
			pst.setString(14, company.getTudiyushenshijian());
			pst.setString(15, company.getBeianwenjian());
			pst.setString(16, company.getBeianshijian());
			pst.setString(17, company.getHuanpingwenjian());
			pst.setString(18, company.getHuanpingshijian());
			pst.setString(19, company.getTudichuranghetongwenjian());
			pst.setString(20, company.getTudichuranghetongshijian());
			pst.setString(21, company.getJiansheyongdixukewenjian());
			pst.setString(22, company.getJiansheyongdixukeshijian());
			pst.setString(23, company.getJianzhugongchengwenjian());
			pst.setString(24, company.getJianzhugongchengshijian());
			pst.setString(25, company.getShigongxukewenjian());
			pst.setString(26, company.getShigongxukeshijian());
			pst.setString(27, company.getWanchengtouzi());
			pst.setString(28, company.getKaigongmianji());
			pst.setString(29, company.getDangqianjinzhan());
			pst.setString(30, company.getJianshedanwei());
			pst.setString(31, company.getLirun());
			pst.setString(32, company.getShuishou());
			pst.setString(33, company.getQiyemingcheng());
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		if (!ret)
			return ret;
		
		if (!deleteXiangmushuxing(company.getQiyemingcheng()))
			return false;
		if (!deleteQiyefenlei(company.getQiyemingcheng()))
			return false;

		for (String str: company.getXiangmushuxing()) {
			if (!addQiyexinxi_Xiangmushuxing(company.getQiyemingcheng(), str))
				return false;
		}
		for (String str: company.getQiyefenlei()) {
			if (!addQiyexinxi_Qiyefenlei(company.getQiyemingcheng(), str))
				return false;
		}
		return ret;		
	}

	public boolean updateTimeNode(String name, TimeNode timeNode) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "update shijianjiedian set description=?"
				+ "where qiyexinxi_id=(select id from qiyexinxi where qiyemingcheng=?) and year=? and month=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, timeNode.getDescription());
			pst.setString(2, name);
			pst.setInt(3, timeNode.getYear());
			pst.setInt(4, timeNode.getMonth());
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;
	}
	
	public boolean deleteQiyexinxi(String qiyemingcheng) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "delete from qiyexinxi where qiyemingcheng=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, qiyemingcheng);
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;

	}
	
	public boolean deleteTimeNode(String qiyemingcheng) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "delete from shijianjiedian "
				+ "where qiyexinxi_id=(select id from qiyexinxi where qiyemingcheng=?)";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, qiyemingcheng);
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;

	}
	
	public boolean deleteXiangmushuxing(String qiyemingcheng) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "delete from qiyexinxi_xiangmushuxing where qiyexinxi_id="
				+ "(select id from qiyexinxi where qiyemingcheng=?)";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, qiyemingcheng);
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;
	}
	
	public boolean deleteQiyefenlei(String qiyemingcheng) {
		boolean ret = true;
		con = new DBConnection().getConn();
		String sql = "delete from qiyexinxi_qiyefenlei where qiyexinxi_id="
				+ "(select id from qiyexinxi where qiyemingcheng=?)";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, qiyemingcheng);
			pst.execute();
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;
	}

	public boolean hasCompany(String name) {
		boolean ret = false;
		con = new DBConnection().getConn();
		String sql = "select * from qiyexinxi where qiyemingcheng=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			rs = pst.executeQuery();
			if (rs.next())
				ret = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;
	}

	public boolean hasTimeNode(String name, int year, int month) {
		boolean ret = false;
		con = new DBConnection().getConn();
		String sql = "select * from qiyexinxi,shijianjiedian where qiyexinxi.id=shijianjiedian.qiyexinxi_id and qiyexinxi.qiyemingcheng=? and "
				+ "shijianjiedian.year=? and shijianjiedian.month=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			rs = pst.executeQuery();
			if (rs.next())
				ret = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;
	}

	public List<String> findAllCompanyNames() {
		List<String> ret = new ArrayList<String>();
		con = new DBConnection().getConn();
		String sql = "select qiyemingcheng from qiyexinxi";
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next())
				ret.add(rs.getString(1));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return ret;
	}

	public Company findCompany(String name) {
		Company company = null;
		con = new DBConnection().getConn();
		String sql = "select id,qiyemingcheng,jianshedanwei,zhandi,zhuceziben,zongtouzi,nianchanzhi,lirun,shuishou,yonggong,"
				+ "baoxiangmufuzeren,qiyelianxiren,lianxirendianhua,beizhu,jianjie,zhucewenjian,zhuceshijian,tudiyushenwenjian,"
				+ "tudiyushenshijian,beianwenjian,beianshijian,huanpingwenjian,huanpingshijian,"
				+ "tudichuranghetongwenjian,tudichuranghetongshijian,jiansheyongdixukewenjian,"
				+ "jiansheyongdixukeshijian,jianzhugongchengwenjian,jianzhugongchengshijian,shigongxukewenjian,"
				+ "shigongxukeshijian,wanchengtouzi,kaigongmianji,dangqianjinzhan from qiyexinxi where qiyemingcheng=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			rs = pst.executeQuery();
			if (rs.next()){
				company = new Company();
				company.setId(rs.getInt(1));
				company.setQiyemingcheng(rs.getString(2));
				company.setJianshedanwei(rs.getString(3));
				company.setZhandi(rs.getString(4));
				company.setZhuceziben(rs.getString(5));
				company.setZongtouzi(rs.getString(6));
				company.setNianchanzhi(rs.getString(7));
				company.setLirun(rs.getString(8));
				company.setShuishou(rs.getString(9));
				company.setYonggong(rs.getString(10));
				company.setBaoxiangmufuzeren(rs.getString(11));
				company.setQiyelianxiren(rs.getString(12));
				company.setLianxirendianhua(rs.getString(13));
				company.setBeizhu(rs.getString(14));
				company.setJianjie(rs.getString(15));
				company.setZhucewenjian(rs.getString(16));
				company.setZhuceshijian(rs.getString(17));
				company.setTudiyushenwenjian(rs.getString(18));
				company.setTudiyushenshijian(rs.getString(19));
				company.setBeianwenjian(rs.getString(20));
				company.setBeianshijian(rs.getString(21));
				company.setHuanpingwenjian(rs.getString(22));
				company.setHuanpingshijian(rs.getString(23));
				company.setTudichuranghetongwenjian(rs.getString(24));
				company.setTudichuranghetongshijian(rs.getString(25));
				company.setJiansheyongdixukewenjian(rs.getString(26));
				company.setJiansheyongdixukeshijian(rs.getString(27));
				company.setJianzhugongchengwenjian(rs.getString(28));
				company.setJianzhugongchengshijian(rs.getString(29));
				company.setShigongxukewenjian(rs.getString(30));
				company.setShigongxukeshijian(rs.getString(31));
				company.setWanchengtouzi(rs.getString(32));
				company.setKaigongmianji(rs.getString(33));
				company.setDangqianjinzhan(rs.getString(34)); 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		if (company == null)
			return company;
		
		List<String> xiangmushuxing = findXiangmushuxing(company.getQiyemingcheng());
		company.setXiangmushuxing(xiangmushuxing);
		
		List<String> qiyefenlei = findQiyefenlei(company.getQiyemingcheng());
		company.setQiyefenlei(qiyefenlei);
		
		return company;
	}
	
	public List<Company> findAllCompanies() {
		List<Company> companies = new ArrayList<Company>();
		Company company;
		List<String> xmsx;
		List<String> qyfl;
		con = new DBConnection().getConn();
		String sql = "select id,qiyemingcheng,jianshedanwei,zhandi,zhuceziben,zongtouzi,nianchanzhi,lirun,shuishou,yonggong,"
				+ "baoxiangmufuzeren,qiyelianxiren,lianxirendianhua,beizhu,jianjie,zhucewenjian,zhuceshijian,tudiyushenwenjian,"
				+ "tudiyushenshijian,beianwenjian,beianshijian,huanpingwenjian,huanpingshijian,"
				+ "tudichuranghetongwenjian,tudichuranghetongshijian,jiansheyongdixukewenjian,"
				+ "jiansheyongdixukeshijian,jianzhugongchengwenjian,jianzhugongchengshijian,shigongxukewenjian,"
				+ "shigongxukeshijian,wanchengtouzi,kaigongmianji,dangqianjinzhan "
				+ "from qiyexinxi";
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				company = new Company();
				company.setId(rs.getInt("id"));
				company.setQiyemingcheng(rs.getString("qiyemingcheng"));
				company.setJianshedanwei(rs.getString("jianshedanwei"));
				company.setZhandi(rs.getString("zhandi"));
				company.setZhuceziben(rs.getString("zhuceziben"));
				company.setZongtouzi(rs.getString("zongtouzi"));
				company.setNianchanzhi(rs.getString("nianchanzhi"));
				company.setLirun(rs.getString("lirun"));
				company.setShuishou(rs.getString("shuishou"));
				company.setYonggong(rs.getString("yonggong"));
				company.setBaoxiangmufuzeren(rs.getString("baoxiangmufuzeren"));
				company.setQiyelianxiren(rs.getString("qiyelianxiren"));
				company.setLianxirendianhua(rs.getString("lianxirendianhua"));
				company.setBeizhu(rs.getString("beizhu"));
				company.setJianjie(rs.getString("jianjie"));
				company.setZhucewenjian(rs.getString("zhucewenjian"));
				company.setZhuceshijian(rs.getString("zhuceshijian"));
				company.setTudiyushenwenjian(rs.getString("tudiyushenwenjian"));
				company.setTudiyushenshijian(rs.getString("tudiyushenshijian"));
				company.setBeianwenjian(rs.getString("beianwenjian"));
				company.setBeianshijian(rs.getString("beianshijian"));
				company.setHuanpingwenjian(rs.getString("huanpingwenjian"));
				company.setHuanpingshijian(rs.getString("huanpingshijian"));
				company.setTudichuranghetongwenjian(rs.getString("tudichuranghetongwenjian"));
				company.setTudichuranghetongshijian(rs.getString("tudichuranghetongshijian"));
				company.setJiansheyongdixukewenjian(rs.getString("jiansheyongdixukewenjian"));
				company.setJiansheyongdixukeshijian(rs.getString("jiansheyongdixukeshijian"));
				company.setJianzhugongchengwenjian(rs.getString("jianzhugongchengwenjian"));
				company.setJianzhugongchengshijian(rs.getString("jianzhugongchengshijian"));
				company.setShigongxukewenjian(rs.getString("shigongxukewenjian"));
				company.setShigongxukeshijian(rs.getString("shigongxukeshijian"));
				company.setWanchengtouzi(rs.getString("wanchengtouzi"));
				company.setKaigongmianji(rs.getString("kaigongmianji"));
				company.setDangqianjinzhan(rs.getString("dangqianjinzhan"));
				xmsx = findXiangmushuxing(company.getQiyemingcheng());
				company.setXiangmushuxing(xmsx);
				qyfl = findQiyefenlei(company.getQiyemingcheng());
				company.setQiyefenlei(qyfl);
				companies.add(company);
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		
		return companies;
	}
	
	public List<Company> findCompaniesByXiangmushuxing(String xiangmushuxing) {
		List<Company> companies = new ArrayList<Company>();
		Company company;
		List<String> xmsx;
		List<String> qyfl;
		con = new DBConnection().getConn();
		String sql = "select q.id,qiyemingcheng,jianshedanwei,zhandi,zhuceziben,zongtouzi,nianchanzhi,lirun,shuishou,yonggong,"
				+ "baoxiangmufuzeren,qiyelianxiren,lianxirendianhua,beizhu,jianjie,zhucewenjian,zhuceshijian,tudiyushenwenjian,"
				+ "tudiyushenshijian,beianwenjian,beianshijian,huanpingwenjian,huanpingshijian,"
				+ "tudichuranghetongwenjian,tudichuranghetongshijian,jiansheyongdixukewenjian,"
				+ "jiansheyongdixukeshijian,jianzhugongchengwenjian,jianzhugongchengshijian,shigongxukewenjian,"
				+ "shigongxukeshijian,wanchengtouzi,kaigongmianji,dangqianjinzhan "
				+ "from qiyexinxi as q,qiyexinxi_xiangmushuxing as qx,xiangmushuxing as x "
				+ "where q.id=qx.qiyexinxi_id and x.id=qx.xiangmushuxing_id and x.name=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, xiangmushuxing);
			rs = pst.executeQuery();
			while (rs.next()) {
				company = new Company();
				company.setId(rs.getInt("id"));
				company.setQiyemingcheng(rs.getString("qiyemingcheng"));
				company.setJianshedanwei(rs.getString("jianshedanwei"));
				company.setZhandi(rs.getString("zhandi"));
				company.setZhuceziben(rs.getString("zhuceziben"));
				company.setZongtouzi(rs.getString("zongtouzi"));
				company.setNianchanzhi(rs.getString("nianchanzhi"));
				company.setLirun(rs.getString("lirun"));
				company.setShuishou(rs.getString("shuishou"));
				company.setYonggong(rs.getString("yonggong"));
				company.setBaoxiangmufuzeren(rs.getString("baoxiangmufuzeren"));
				company.setQiyelianxiren(rs.getString("qiyelianxiren"));
				company.setLianxirendianhua(rs.getString("lianxirendianhua"));
				company.setBeizhu(rs.getString("beizhu"));
				company.setJianjie(rs.getString("jianjie"));
				company.setZhucewenjian(rs.getString("zhucewenjian"));
				company.setZhuceshijian(rs.getString("zhuceshijian"));
				company.setTudiyushenwenjian(rs.getString("tudiyushenwenjian"));
				company.setTudiyushenshijian(rs.getString("tudiyushenshijian"));
				company.setBeianwenjian(rs.getString("beianwenjian"));
				company.setBeianshijian(rs.getString("beianshijian"));
				company.setHuanpingwenjian(rs.getString("huanpingwenjian"));
				company.setHuanpingshijian(rs.getString("huanpingshijian"));
				company.setTudichuranghetongwenjian(rs.getString("tudichuranghetongwenjian"));
				company.setTudichuranghetongshijian(rs.getString("tudichuranghetongshijian"));
				company.setJiansheyongdixukewenjian(rs.getString("jiansheyongdixukewenjian"));
				company.setJiansheyongdixukeshijian(rs.getString("jiansheyongdixukeshijian"));
				company.setJianzhugongchengwenjian(rs.getString("jianzhugongchengwenjian"));
				company.setJianzhugongchengshijian(rs.getString("jianzhugongchengshijian"));
				company.setShigongxukewenjian(rs.getString("shigongxukewenjian"));
				company.setShigongxukeshijian(rs.getString("shigongxukeshijian"));
				company.setWanchengtouzi(rs.getString("wanchengtouzi"));
				company.setKaigongmianji(rs.getString("kaigongmianji"));
				company.setDangqianjinzhan(rs.getString("dangqianjinzhan")); 
//				if ((xmsx = findXiangmushuxing(company.getQiyemingcheng())) != null)
				xmsx = findXiangmushuxing(company.getQiyemingcheng());
				company.setXiangmushuxing(xmsx);
				qyfl = findQiyefenlei(company.getQiyemingcheng());
				company.setQiyefenlei(qyfl);
				companies.add(company);
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}

		return companies;
	}

	public List<Company> findCompaniesByQiyefenlei(String qiyefenlei) {
		List<Company> companies = new ArrayList<Company>();
		Company company;
		List<String> xmsx;
		List<String> qyfl;
		con = new DBConnection().getConn();
		String sql = "select q.id,qiyemingcheng,jianshedanwei,zhandi,zhuceziben,zongtouzi,nianchanzhi,lirun,shuishou,yonggong,"
				+ "baoxiangmufuzeren,qiyelianxiren,lianxirendianhua,beizhu,jianjie,zhucewenjian,zhuceshijian,tudiyushenwenjian,"
				+ "tudiyushenshijian,beianwenjian,beianshijian,huanpingwenjian,huanpingshijian,"
				+ "tudichuranghetongwenjian,tudichuranghetongshijian,jiansheyongdixukewenjian,"
				+ "jiansheyongdixukeshijian,jianzhugongchengwenjian,jianzhugongchengshijian,shigongxukewenjian,"
				+ "shigongxukeshijian,wanchengtouzi,kaigongmianji,dangqianjinzhan "
				+ "from qiyexinxi as q,qiyexinxi_qiyefenlei as qf,qiyefenlei as f "
				+ "where q.id=qf.qiyexinxi_id and f.id=qf.qiyefenlei_id and f.name=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, qiyefenlei);
			rs = pst.executeQuery();
			//			System.out.println(rs.next());
			while (rs.next()) {
				company = new Company();
				company.setId(rs.getInt("id"));
				company.setQiyemingcheng(rs.getString("qiyemingcheng"));
				company.setJianshedanwei(rs.getString("jianshedanwei"));
				company.setZhandi(rs.getString("zhandi"));
				company.setZhuceziben(rs.getString("zhuceziben"));
				company.setZongtouzi(rs.getString("zongtouzi"));
				company.setNianchanzhi(rs.getString("nianchanzhi"));
				company.setLirun(rs.getString("lirun"));
				company.setShuishou(rs.getString("shuishou"));
				company.setYonggong(rs.getString("yonggong"));
				company.setBaoxiangmufuzeren(rs.getString("baoxiangmufuzeren"));
				company.setQiyelianxiren(rs.getString("qiyelianxiren"));
				company.setLianxirendianhua(rs.getString("lianxirendianhua"));
				company.setBeizhu(rs.getString("beizhu"));
				company.setJianjie(rs.getString("jianjie"));
				company.setZhucewenjian(rs.getString("zhucewenjian"));
				company.setZhuceshijian(rs.getString("zhuceshijian"));
				company.setTudiyushenwenjian(rs.getString("tudiyushenwenjian"));
				company.setTudiyushenshijian(rs.getString("tudiyushenshijian"));
				company.setBeianwenjian(rs.getString("beianwenjian"));
				company.setBeianshijian(rs.getString("beianshijian"));
				company.setHuanpingwenjian(rs.getString("huanpingwenjian"));
				company.setHuanpingshijian(rs.getString("huanpingshijian"));
				company.setTudichuranghetongwenjian(rs.getString("tudichuranghetongwenjian"));
				company.setTudichuranghetongshijian(rs.getString("tudichuranghetongshijian"));
				company.setJiansheyongdixukewenjian(rs.getString("jiansheyongdixukewenjian"));
				company.setJiansheyongdixukeshijian(rs.getString("jiansheyongdixukeshijian"));
				company.setJianzhugongchengwenjian(rs.getString("jianzhugongchengwenjian"));
				company.setJianzhugongchengshijian(rs.getString("jianzhugongchengshijian"));
				company.setShigongxukewenjian(rs.getString("shigongxukewenjian"));
				company.setShigongxukeshijian(rs.getString("shigongxukeshijian"));
				company.setWanchengtouzi(rs.getString("wanchengtouzi"));
				company.setKaigongmianji(rs.getString("kaigongmianji"));
				company.setDangqianjinzhan(rs.getString("dangqianjinzhan")); 
				//				if ((xmsx = findXiangmushuxing(company.getQiyemingcheng())) != null)
				xmsx = findXiangmushuxing(company.getQiyemingcheng());
				company.setXiangmushuxing(xmsx);
				qyfl = findQiyefenlei(company.getQiyemingcheng());
				company.setQiyefenlei(qyfl);
				companies.add(company);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}

		return companies;
	}
	
	public List<Company> findCompaniesByXiangmushuxingAndQiyefenlei(String xiangmushuxing, String qiyefenlei) {
		List<Company> companies = new ArrayList<Company>();
		Company company;
		List<String> xmsx;
		List<String> qyfl;
		con = new DBConnection().getConn();
		String sql = "select q.id,qiyemingcheng,jianshedanwei,zhandi,zhuceziben,zongtouzi,nianchanzhi,lirun,shuishou,yonggong,"
				+ "baoxiangmufuzeren,qiyelianxiren,lianxirendianhua,beizhu,jianjie,zhucewenjian,zhuceshijian,tudiyushenwenjian,"
				+ "tudiyushenshijian,beianwenjian,beianshijian,huanpingwenjian,huanpingshijian,"
				+ "tudichuranghetongwenjian,tudichuranghetongshijian,jiansheyongdixukewenjian,"
				+ "jiansheyongdixukeshijian,jianzhugongchengwenjian,jianzhugongchengshijian,shigongxukewenjian,"
				+ "shigongxukeshijian,wanchengtouzi,kaigongmianji,dangqianjinzhan "
				+ "from qiyexinxi as q,qiyexinxi_qiyefenlei as qf,qiyefenlei as f,qiyexinxi_xiangmushuxing as qx,xiangmushuxing as x "
				+ "where q.id=qf.qiyexinxi_id and f.id=qf.qiyefenlei_id and f.name=? "
				+ "and q.id=qx.qiyexinxi_id and x.id = qx.xiangmushuxing_id and x.name=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, qiyefenlei);
			pst.setString(2, xiangmushuxing);
			rs = pst.executeQuery();
			//			System.out.println(rs.next());
			while (rs.next()) {
				company = new Company();
				company.setId(rs.getInt("id"));
				company.setQiyemingcheng(rs.getString("qiyemingcheng"));
				company.setJianshedanwei(rs.getString("jianshedanwei"));
				company.setZhandi(rs.getString("zhandi"));
				company.setZhuceziben(rs.getString("zhuceziben"));
				company.setZongtouzi(rs.getString("zongtouzi"));
				company.setNianchanzhi(rs.getString("nianchanzhi"));
				company.setLirun(rs.getString("lirun"));
				company.setShuishou(rs.getString("shuishou"));
				company.setYonggong(rs.getString("yonggong"));
				company.setBaoxiangmufuzeren(rs.getString("baoxiangmufuzeren"));
				company.setQiyelianxiren(rs.getString("qiyelianxiren"));
				company.setLianxirendianhua(rs.getString("lianxirendianhua"));
				company.setBeizhu(rs.getString("beizhu"));
				company.setJianjie(rs.getString("jianjie"));
				company.setZhucewenjian(rs.getString("zhucewenjian"));
				company.setZhuceshijian(rs.getString("zhuceshijian"));
				company.setTudiyushenwenjian(rs.getString("tudiyushenwenjian"));
				company.setTudiyushenshijian(rs.getString("tudiyushenshijian"));
				company.setBeianwenjian(rs.getString("beianwenjian"));
				company.setBeianshijian(rs.getString("beianshijian"));
				company.setHuanpingwenjian(rs.getString("huanpingwenjian"));
				company.setHuanpingshijian(rs.getString("huanpingshijian"));
				company.setTudichuranghetongwenjian(rs.getString("tudichuranghetongwenjian"));
				company.setTudichuranghetongshijian(rs.getString("tudichuranghetongshijian"));
				company.setJiansheyongdixukewenjian(rs.getString("jiansheyongdixukewenjian"));
				company.setJiansheyongdixukeshijian(rs.getString("jiansheyongdixukeshijian"));
				company.setJianzhugongchengwenjian(rs.getString("jianzhugongchengwenjian"));
				company.setJianzhugongchengshijian(rs.getString("jianzhugongchengshijian"));
				company.setShigongxukewenjian(rs.getString("shigongxukewenjian"));
				company.setShigongxukeshijian(rs.getString("shigongxukeshijian"));
				company.setWanchengtouzi(rs.getString("wanchengtouzi"));
				company.setKaigongmianji(rs.getString("kaigongmianji"));
				company.setDangqianjinzhan(rs.getString("dangqianjinzhan")); 
				//				if ((xmsx = findXiangmushuxing(company.getQiyemingcheng())) != null)
				xmsx = findXiangmushuxing(company.getQiyemingcheng());
				company.setXiangmushuxing(xmsx);
				qyfl = findQiyefenlei(company.getQiyemingcheng());	// 
				company.setQiyefenlei(qyfl);
				companies.add(company);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}

		return companies;
	}

	public List<String> findXiangmushuxing(String qiyemingcheng) {
		List<String> ret = new ArrayList<String>();
		Connection con1 = new DBConnection().getConn();
		PreparedStatement pst1 = null;
		ResultSet rs1 = null;
		String sql = "select x.name from xiangmushuxing as x,qiyexinxi as q,qiyexinxi_xiangmushuxing as qx where "
				+ "q.qiyemingcheng=? and q.id=qx.qiyexinxi_id and x.id=qx.xiangmushuxing_id";
		try {
			pst1 = con1.prepareStatement(sql);
			pst1.setString(1, qiyemingcheng);
			rs1 = pst1.executeQuery();
			while (rs1.next())
				ret.add(rs1.getString(1));
		} catch (SQLException e){
			e.printStackTrace();
		} finally {
			try {
				if (rs1 != null) {
					rs1.close();
					rs1 = null;
				}
				if (pst1 != null) {
					pst1.close();
					pst1 = null;
				}				
				if (con1 != null) {
					con1.close();
					con1 = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return ret;
	}
	
	public List<String> findQiyefenlei(String qiyemingcheng) {
		List<String> ret = new ArrayList<String>();
		Connection con1 = new DBConnection().getConn();
		PreparedStatement pst1 = null;
		ResultSet rs1 = null;
		String sql = "select f.name from qiyefenlei as f,qiyexinxi as q,qiyexinxi_qiyefenlei as qf where "
				+ "q.qiyemingcheng=? and q.id=qf.qiyexinxi_id and f.id=qf.qiyefenlei_id";
		try {
			pst1 = con1.prepareStatement(sql);
			pst1.setString(1, qiyemingcheng);
			rs1 = pst1.executeQuery();
			while (rs1.next())
				ret.add(rs1.getString(1));
		} catch (SQLException e){
			e.printStackTrace();
		} finally {
			try {
				if (rs1 != null) {
					rs1.close();
					rs1 = null;
				}
				if (pst1 != null) {
					pst1.close();
					pst1 = null;
				}				
				if (con1 != null) {
					con1.close();
					con1 = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return ret;
	}

	public TimeNode findTimeNode(String name, int year, int month) {
		TimeNode timeNode = null;
		con = new DBConnection().getConn();
		String sql = "select id,qiyexinxi_id,year,month,description from shijianjiedian "
				+ "where qiyexinxi_id=(select id from qiyexinxi where qiyemingcheng=?) and year=? and month=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			pst.setInt(2, year);
			pst.setInt(3, month);
			rs = pst.executeQuery();
			if (rs.next()) {
				timeNode = new TimeNode();
				timeNode.setId(rs.getInt(1));
				timeNode.setQiyexinxi_id(rs.getInt(2));
				timeNode.setYear(rs.getInt(3));
				timeNode.setMonth(rs.getInt(4));
				timeNode.setDescription(rs.getString(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		
		return timeNode;
	}

	public List<TimeNode> findTimeNodes(String name, int year) {
		List<TimeNode> timeNodes = new ArrayList<TimeNode>();;
		con = new DBConnection().getConn();
		String sql = "select id,qiyexinxi_id,year,month,description from shijianjiedian "
				+ "where qiyexinxi_id=(select id from qiyexinxi where qiyemingcheng=?) and year=? "
				+ "order by year,month asc";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			pst.setInt(2, year);
			rs = pst.executeQuery();
			while (rs.next()) {
				TimeNode timeNode = new TimeNode();
				timeNode.setId(rs.getInt(1));
				timeNode.setQiyexinxi_id(rs.getInt(2));
				timeNode.setYear(rs.getInt(3));
				timeNode.setMonth(rs.getInt(4));
				timeNode.setDescription(rs.getString(5));
				timeNodes.add(timeNode);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}

		return timeNodes;
	}
	
	public List<TimeNode> findTimeNodes(String name) {
		List<TimeNode> timeNodes = new ArrayList<TimeNode>();
		con = new DBConnection().getConn();
		String sql = "select id,qiyexinxi_id,year,month,description from shijianjiedian "
				+ "where qiyexinxi_id=(select id from qiyexinxi where qiyemingcheng=?) "
				+ "order by year,month asc";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			rs = pst.executeQuery();
			while (rs.next()) {
				TimeNode timeNode = new TimeNode();
				timeNode.setId(rs.getInt(1));
				timeNode.setQiyexinxi_id(rs.getInt(2));
				timeNode.setYear(rs.getInt(3));
				timeNode.setMonth(rs.getInt(4));
				timeNode.setDescription(rs.getString(5));
				timeNodes.add(timeNode);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}

		return timeNodes;
	}
	
	public TimeNode findFirstTimeNode(String name) {
		TimeNode timeNode = null;
		con = new DBConnection().getConn();
		String sql = "select id,qiyexinxi_id,year,month,description from shijianjiedian "
				+ "where qiyexinxi_id=(select id from qiyexinxi where qiyemingcheng=?) "
				+ "order by year,month asc";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			rs = pst.executeQuery();
			if (rs.next()) {
				timeNode = new TimeNode();
				timeNode.setId(rs.getInt(1));
				timeNode.setQiyexinxi_id(rs.getInt(2));
				timeNode.setYear(rs.getInt(3));
				timeNode.setMonth(rs.getInt(4));
				timeNode.setDescription(rs.getString(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		
		return timeNode;
	}

	public void close() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (pst != null) {
				pst.close();
				pst = null;
			}
			if (con != null) {
				con.close();
				con = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
